package tech.aistar.dao.impl;

import tech.aistar.dao.IVideoDao;
import tech.aistar.entity.User;
import tech.aistar.entity.Video;
import tech.aistar.util.JdbcTemplate;
import tech.aistar.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 本类用来演示:
 *
 * @author: success
 * @date: 2022/8/12 8:46 上午
 */
public class VideoDaoImpl implements IVideoDao {
    @Override
    public List<User> findAllWithTwoSql() {
        Connection conn = null;
        PreparedStatement pst1 = null;
        PreparedStatement pst2 = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;

        List<User> users = new ArrayList<>();

        try {
            conn = JdbcUtil.getConnection();

            String sql = "select * from jdbc_user";
            pst1 = conn.prepareStatement(sql);
            rs1 = pst1.executeQuery();

            String sql2 = "select * from jdbc_video where user_id=?";
            pst2 = conn.prepareStatement(sql2);

            //遍历
            while(rs1.next()){

                Integer userId = rs1.getInt(1);
                String username = rs1.getString(2);
                String pwd = rs1.getString(3);
                Date birthday = rs1.getDate(4);
                Integer power = rs1.getInt(5);
                User user = new User(username,pwd,birthday,power);
                user.setId(userId);

                //维护了一个videoList
                //pst2发送参数
                pst2.setInt(1,userId);
                rs2 = pst2.executeQuery();

                //判断一下这个用户是否发布了视频
                List<Video> videos = new ArrayList<>();
                while(rs2.next()){
                    Video video = new Video();
                    video.setId(rs2.getInt(1));
                    video.setTitle(rs2.getString(2));
                    video.setContent(rs2.getString(3));
                    //双向绑定
                    video.setUser(user);
                    videos.add(video);
                }
                user.setVideoList(videos);
                users.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(null,pst2,rs2);
            JdbcUtil.close(conn,pst1,rs1);
        }
        return users;
    }

    @SuppressWarnings("all")
    @Override
    public List<User> findAllWithOneSql() {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Map<Integer,User> maps = new HashMap<>();
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select u.*,v.id vid,v.title,v.content from jdbc_user u left join jdbc_video v on u.id = v.user_id";
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();

            //定义一个map集合
            //key  用户id
            //value User

            while(rs.next()){
                //获取用户id = key
                Integer userId = rs.getInt(1);
                //判断maps集合中是否包含了该用户
                if(maps.containsKey(userId)){
                    //根据key获取用户
                    User u = maps.get(userId);

                    Video video = new Video();
                    video.setId(rs.getInt("vid"));
                    video.setTitle(rs.getString("title"));
                    video.setContent(rs.getString("content"));
                    //双向绑定
                    video.setUser(u);

                    u.getVideoList().add(video);

                }else{
                    String username = rs.getString(2);
                    String pwd = rs.getString(3);
                    Date birthday = rs.getDate(4);
                    Integer power = rs.getInt(5);
                    User user = new User(username,pwd,birthday,power);
                    user.setId(userId);

                    //判断该用户是否发布了视频
                    String title = rs.getString("title");
                    if(null!=title){
                        List<Video> videos = new ArrayList<>();
                        Video video = new Video();
                        video.setId(rs.getInt("vid"));
                        video.setTitle(rs.getString("title"));
                        video.setContent(rs.getString("content"));
                        //双向绑定
                        video.setUser(user);
                        videos.add(video);

                        //视频绑定到user中
                        user.setVideoList(videos);
                    }

                    //放入到maps集合中
                    maps.put(userId,user);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn,pst,rs);
        }
        //利用map集合的所有的value来构建一个list
        return new ArrayList<>(maps.values());
    }
}
